﻿using Npgsql;
using NpgsqlTypes;
using OSGeo.OGR;
using shp2postgres.gdal;
using shp2postgres.log;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace shp2postgres.sql
{
    public class SQLTools
    {

        SqlConnection sqlConnection = null;
        NpgsqlConnection npgsqlConnection = null;
        private NpgsqlCommand npgsqlCommand = null;
        private SqlCommand sqlCommand = null;

        private string dbName;
        private DabaBaseType dbType;

        Dictionary<String, List<FieldModel>> tableFieldMap = new Dictionary<string, List<FieldModel>>();



        public bool ConnectSql(DabaBaseType dbType, string url, string dbName, string user, string pwd)
        {
            this.dbName = dbName;
            this.dbType = dbType;
            switch (dbType)
            {
                case DabaBaseType.PostgreSQL: return ConnectPgSql(url, dbName, user, pwd);
                case DabaBaseType.SQLServer: return ConnectSQLServer(url, dbName, user, pwd);
                default: return false;
            }
        }

        private bool ConnectSQLServer(string url, string dbName, string user, string pwd)
        {
            if (null == sqlConnection)
            {
                var connStr = new StringBuilder()
                    .AppendFormat("Data Source={0};", url)
                    .AppendFormat("User ID={0};", dbName)
                    .AppendFormat("Pwd={0};", user)
                    .AppendFormat("Initial Catalog={0}", pwd)
                    .ToString();
                sqlConnection = new SqlConnection(connStr);
            }
            try
            {
                sqlConnection.Open();
                Log.InfoAsync($"SQLServer数据库连接成功, url : {url},  db : {dbName}");
            }
            catch (Exception e)
            {
                Log.InfoAsync("连接不到指定的数据库，请检查各项连接参数");
                Log.ErrorAsync(e.Message);
                MessageBox.Show("数据库连接中断！");
                return false;
            }
            return true;
        }

        private bool ConnectPgSql(string url, string dbName, string user, string pwd)
        {
            string constr = $"Host={url};Database={dbName};Username={user};Password={pwd}";
            Log.InfoAsync(constr);
            if (null == npgsqlConnection)
            {
                npgsqlConnection = new NpgsqlConnection(constr);
            }
            try
            {
                npgsqlConnection.Open();
                Log.InfoAsync($"Postgres数据库连接成功, url : {url},  db : {dbName}");
            }
            catch (Exception e)
            {
                Log.InfoAsync("连接不到指定的数据库，请检查各项连接参数");
                Log.ErrorAsync(e.Message);
                MessageBox.Show("数据库连接中断！");
                return false;
            }
            npgsqlCommand = new NpgsqlCommand();
            npgsqlCommand.Connection = npgsqlConnection;

            return true;
        }

        /// <summary>
        /// 检查数据表是否存在
        /// </summary>
        /// <param name="table"></param>
        /// <returns>存在返回true，否则false</returns>
        public bool checkTableExist(string table)
        {

            switch (dbType)
            {
                case DabaBaseType.PostgreSQL: 
                    if (null == npgsqlConnection || npgsqlConnection.State != System.Data.ConnectionState.Open) { MessageBox.Show("请先选择连接数据库"); return false; }
                    npgsqlCommand.CommandText = $"select count(*) from information_schema.tables where table_schema='public' and table_name='{table}';";
                    long result = (long) npgsqlCommand.ExecuteScalar();
                    return 1 == result;
                case DabaBaseType.SQLServer: return true;
                default: return false;
            }
        }

        public DataSet getTableInfo(string table)
        {
            switch (dbType)
            {
                case DabaBaseType.PostgreSQL:
                    npgsqlCommand.CommandText = $"select column_name,data_type from information_schema.columns where table_name = '{table}'";
                    try
                    {
                        using (NpgsqlDataAdapter dataAdapter = new NpgsqlDataAdapter(npgsqlCommand))
                        {
                            DataSet dataSet = new DataSet();
                            dataAdapter.Fill(dataSet);
                            
                            return dataSet;
                        }
                    } catch (Exception e)
                    {
                        Log.ErrorAsync(e.Message);
                        return null;
                    }
                    
                case DabaBaseType.SQLServer:
                    return null;
                default:
                    return null;
            }
        }

        /// <summary>
        /// shp数据导入到数据库，前提已有数据表
        /// </summary>
        /// <param name="table">数据表名称</param>
        /// <param name="fieldModels">属性字段对应关系</param>
        /// <param name="properties">shp数据属性集合</param>
        /// <param name="geometries">shp数据空间数据集合</param>
        /// <returns></returns>
        public int Import(string table, List<FieldModel> fieldModels, List<Dictionary<String, Object>> properties, List<Geometry> geometries)
        {
            Log.InfoAsync("开始导入shp至数据表{0}，总数据{1}条", new String[] {table, geometries.Count.ToString()});
            StringBuilder sb = new StringBuilder("保留字段：\n");
            foreach (var fieldModel in fieldModels)
            {
                if(fieldModel.Necessary && !fieldModel.IsGeom)
                    sb.Append(fieldModel.GdalField).Append(" -> ").Append(fieldModel.SqlField).Append("\t");
            }
            Log.InfoAsync(sb.ToString());
            int result = 0;
            switch (dbType)
            {
                case DabaBaseType.PostgreSQL:
                    result = ImportPostgres(table, fieldModels, properties, geometries);
                    break;
                case DabaBaseType.SQLServer:
                    break;
                default:
                    break;
            }
            return result;
        }

        /// <summary>
        /// 导入数据到Postgres数据库
        /// </summary>
        /// <param name="table"></param>
        /// <param name="fieldModels"></param>
        /// <param name="properties"></param>
        /// <param name="geometries"></param>
        /// <returns></returns>
        private int ImportPostgres(string table, List<FieldModel> fieldModels, List<Dictionary<String, Object>> properties, List<Geometry> geometries)
        {
            // 
            DataTable dataTable = new DataTable();
            dataTable.Columns.Add("id", typeof(int));
            foreach (var fieldModel in fieldModels)
            {
                if (fieldModel.Necessary) dataTable.Columns.Add(fieldModel.SqlField, fieldModel.Type());
            }
            //
            for (int i = 0; i < properties.Count; i++)
            {
                DataRow row = dataTable.NewRow();
                row["id"] = i + 1;
                foreach (var fm in fieldModels)
                {
                    if (fm.Necessary && !fm.IsGeom)
                    {
                        row[fm.SqlField] = properties[i][fm.GdalField];
                    } else if (fm.IsGeom)
                    {
                        Geometry g = geometries[i];
                        byte[] wkb = new byte[g.WkbSize()];
                        g.ExportToWkb(wkb);
                        row[fm.SqlField] = wkb;
                    }
                }
                dataTable.Rows.Add(row);
            }
            Stopwatch stopwatch = Stopwatch.StartNew();
            string strformat = string.Format("COPY {0} FROM STDIN BINARY", table);
            using (var writer = npgsqlConnection.BeginBinaryImport(strformat))
            {
                foreach (DataRow row in dataTable.Rows)
                {
                    //byte[] wkb = (byte[])row["WKB"];
                    //using (MemoryStream stream = new MemoryStream())
                    //{
                    //    // 将 WKB 流写入 MemoryStream 中
                    //    stream.Write(wkb, 0, wkb.Length);
                    //    // 写入几何属性
                    //    writer.Write(stream.ToArray());
                    //    // 写入字段信息
                    //    writer.Write(row["Name"].ToString());
                    //    writer.Write(Convert.ToDouble(row["Value"]));
                    //    // 提交写入操作
                    //    writer.WriteRow();
                    //}
                    writer.WriteRow(row.ItemArray);
                }
                long r = ((long)writer.Complete());
                stopwatch.Stop();
                Log.InfoAsync($"入库完成，总入库 {r} 条数据！ 总用时{stopwatch.Elapsed.TotalSeconds}秒");
            }
            return 0;
        }

        /// <summary>
        /// 清空已有数据表
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        public bool ClearTable(string table)
        {
            switch (dbType)
            {
                case DabaBaseType.PostgreSQL:
                    if(npgsqlConnection.State != ConnectionState.Open) npgsqlConnection.Open();
                    using(NpgsqlCommand comd = new NpgsqlCommand())
                    {
                        comd.Connection = npgsqlConnection;
                        comd.CommandText = $"delete from {table}";
                        comd.ExecuteNonQuery();
                        return true;
                    }
                case DabaBaseType.SQLServer:
                    break;
            }
            return false;
        }
        public bool CreateTable(string table, string epsg, List<FieldModel> filedModels)
        {
            Log.InfoAsync($"开始创建数据表：{table}");
            switch (dbType)
            {
                case DabaBaseType.PostgreSQL:
                    return PostgresCreateTable(table, epsg, filedModels);
                case DabaBaseType.SQLServer:
                    break;

            }
            return false;
        }

        private bool PostgresCreateTable(string table, string epsg, List<FieldModel> filedModels)
        {
            StringBuilder sb = new StringBuilder($"CREATE TABLE {table} (id SERIAL PRIMARY KEY, ");
            string geomFileName = "the_geom";
            foreach (var model in filedModels)
            {
                if (model.IsGeom)
                {
                    geomFileName = model.SqlField;
                    continue;
                }
                if (model.Necessary)
                    sb.Append(model.SqlField + " " + model.SqlFieldType + ", ");
            }
            sb.Remove(sb.Length - 2, 1).Append(");");
            Log.InfoAsync(sb.ToString());

            using (NpgsqlCommand cmd = new NpgsqlCommand())
            {
                if (npgsqlConnection.State != ConnectionState.Open)
                {
                    npgsqlConnection.Open();
                }
                cmd.Connection = npgsqlConnection;
                cmd.CommandText = sb.ToString();
                int result = cmd.ExecuteNonQuery();
                if (result == 0)
                {
                    Log.ErrorAsync($"创建数据表 {table} 失败！");
                    return false;
                }

                string sql = $"select AddGeometryColumn('{table}', '{geomFileName}', {epsg}, 'geometry', 2, true);";
                cmd.CommandText = sql;
                Log.InfoAsync(sql);
                result = cmd.ExecuteNonQuery();
                Log.InfoAsync($"创建空间属性列返回结果：{result}");
                return true;
            }
        }

    }

    public enum DabaBaseType
    {
        PostgreSQL,
        SQLServer,
        MySQL,
        Sqlite
    }
}
